package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.entity.CalculateUse;
import com.entity.TaskRecordItem;

/**   
* @Title: CalculateUseDao.java 
* @Package com.interfaces 
* @author 孙梦楠 
* @date 2017年9月29日 下午1:32:12 
* @version V1.0.0   
*/

public interface CalculateUseDao extends JpaRepository<CalculateUse, Integer> {
	
	@Transactional
	@Query(value="SELECT * FROM Calculate_use tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress=?1) AND (tri.code=?2)" ,nativeQuery=true)
	public List<CalculateUse> findTaskrecorditemByMeter(String meteraddress, String code,String time1,String time2);
	
	@Transactional
	@Query(value="SELECT * FROM Calculate_use tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress IN (SELECT m.meter_address FROM e_meter m WHERE m.meter_org=?1 AND m.type=?5 AND m.meter_status != 4)) AND (tri.code=?2)" ,nativeQuery=true)
	public List<CalculateUse> findTaskrecorditemByMeter2(Integer meterorg, String code,String time1,String time2,String type);

	@Transactional
	@Query(value="SELECT COUNT( DISTINCT tri.meteraddress) FROM Calculate_use tri WHERE (tri.time = ?1 OR  ?1 IS NULL)  "
			+ "AND (tri.meteraddress IN (SELECT m.meter_address FROM e_meter m WHERE m.meter_org=?2 AND m.type=?3 AND m.meter_status != 4)) "
			+ "AND (tri.code=?4) " ,nativeQuery=true)
	public Integer customercountByMeter(String time,Integer meterorg, String type,String code);
	
	List<CalculateUse> findByMeteraddress(String meteraddress);
	
	
	
}
